package fr.inserm.u674.chcdbweb.model.DAO;

import static fr.inserm.u674.chcdbweb.model.DAO.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import fr.inserm.u674.chcdbweb.model.DTO.Tissue;
import fr.inserm.u674.chcdbweb.model.DTO.TissueExtraction;
import fr.inserm.u674.chcdbweb.model.DTO.TissueExtractionRNA;
import fr.inserm.u674.chcdbweb.model.DTO.User;

public class TissueExtractionDAO {

	// CONSTANTS ----------------------------------------------------------------------------------

	private static final String SQL_FIND_BY_EXTRACTIONID =
		"SELECT * FROM Extraction WHERE extractionID = ?";

	private static final String SQL_LIST_ORDER_BY_CHCID_THEN_DATE =
		"SELECT * FROM TissueManipulation tm INNER JOIN TissueExtraction "
			+ "ON (extractionID = manipulationID)  " + "INNER JOIN Tissue t ON "
			+ "(tm.tissue_chcID = t.chcID AND tm.tissue_tissueTypeID = t.tissueTypeID) "
			+ "LEFT OUTER JOIN TissueRNAExtraction trna ON "
			+ "(extractionID = Extraction_extractionID)"
			+ "ORDER BY tissue_chcID, manipulationDate ";

	private static final String SQL_LIST_SELECTSTUB =
		"SELECT * FROM TissueManipulation  tm INNER JOIN TissueExtraction te "
			+ "ON (extractionID = manipulationID) " + "INNER JOIN Tissue t ON "
			+ "(tm.tissue_chcID = t.chcID AND tm.tissue_tissueTypeID = t.tissueTypeID) "
			+ "LEFT OUTER JOIN TissueRNAExtraction trna ON "
			+ "(extractionID = Extraction_extractionID)";
	private static final String SQL_LIST_ORDERBYSTUB = "ORDER BY tissue_chcID, manipulationDate ";

	private static final String SQL_LIST_TISSUE_EXTRACTIONS =
		"SELECT * FROM TissueManipulation tm INNER JOIN TissueExtraction "
			+ "ON (extractionID = manipulationID) " + "INNER JOIN Tissue t ON "
			+ "(tm.tissue_chcID = t.chcID AND tm.tissue_tissueTypeID = t.tissueTypeID) "
			+ "WHERE tissue_chcID = ? AND tissue_tissueTypeID = ? ";

	private static final String SQL_LIST_EXTRACTION_QUERY_SELECT_STUB =
		"SELECT * FROM TissuExtraction te INNER JOIN TissueManipulation tm "
			+ "ON (extractionID = manipulationID) " + "INNER JOIN Tissue t ON "
			+ "(tm.tissue_chcID = t.chcID AND tm.tissue_tissueTypeID = t.tissueTypeID) ";
	private static final String SQL_LIST_EXTRACTION_QUERY_ORDER_STUB =
		" ORDER BY tissue_chcID, tissue_tissueTypeID, dateExtraction ";

	private static final String SQL_INSERT =
		"INSERT INTO Extraction (tissue_chcID, tissue_tissueTypeID, experimenter, dateExtraction,"
			+ " finalVolume_muL, concentration_ng_muL, quantity_mug, extractionComment, "
			+ " extractionType) VALUES (?,?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE =
		"UPDATE Extraction SET tissue_chcID = ?, tissue_tissueTypeID = ?, "
			+ "experimenter = ?, dateExtraction = ?, finalVolume_muL = ?, "
			+ "concentration_ng_muL = ?, quantity_mug = ?, extractionComment = ?, "
			+ "extractionType = ? WHERE extractionID = ?";

	private static final String SQL_EXISTS_EXTRACTION =
		"SELECT COUNT(*) FROM TissueExtraction WHERE extractionID = ?";

	private static final String SQL_EXISTS_SIMILAR_EXTRACTION_BY_DATE_AND_CONCENTRATION =
		"SELECT COUNT(*) FROM TissueExtraction "
			+ "INNER JOIN TissueManipulation ON (extractionID = manipulationID) "
			+ "WHERE tissue_chcID = ? AND tissue_tissueTypeID = ? " + "AND concentration = ? "
			+ "AND manipulationDate = ? AND extractionType = ? ";

	private static final String SQL_LIST_SIMILAR_EXTRACTION_BY_DATE_AND_CONCENTRATION =
		"SELECT * FROM TissueExtraction "
			+ "INNER JOIN TissueManipulation ON (extractionID = manipulationID) "
			+ "LEFT OUTER JOIN TissueRNAExtraction ON (manipulationID = Extraction_extractionID) "
			+ "WHERE tissue_chcID = ? AND tissue_tissueTypeID = ? " + "AND concentration = ? "
			+ "AND manipulationDate = ? AND extractionType = ? ";

	private static final String SQL_DELETE = "DELETE FROM Extraction WHERE extractionID = ?";

	// FIELDS
	// ---------------------------------------------------------------------------------------

	private DAOFactory daoFactory;

	// CONSTRUCTORS -------------------------------------------------------------------------------

	public TissueExtractionDAO(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}

	// ACTIONS ------------------------------------------------------------------------------------

	public TissueExtraction find(Integer extractionID) throws DAOException {
		return find(SQL_FIND_BY_EXTRACTIONID, extractionID);
	}

	private TissueExtraction find(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		TissueExtraction extraction = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement =
				prepareStatement(connection, SQL_FIND_BY_EXTRACTIONID, false, values);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				return mapTissueExtraction(resultSet, false);
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}

		return extraction;
	}

	/**
	 * Returns a list of all extractions from the database ordered by tissue chcID and extraction
	 * date. The list is never null and is empty when the database does not contain any extraction.
	 * 
	 * @param tissuesOfInterest
	 *            TODO
	 * 
	 * @return A list of all extractions from the database ordered by extraction date and chcID.
	 * @throws DAOException
	 *             If something fails at database level.
	 */
	public List<TissueExtraction> list(ArrayList<Tissue> tissuesOfInterest) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<TissueExtraction> extractions = new ArrayList<TissueExtraction>();
		try {
			connection = daoFactory.getConnection();
			if (tissuesOfInterest != null && tissuesOfInterest.size() > 0) {
				String finalSQL = SQL_LIST_SELECTSTUB + " WHERE ( ";
				int tissueCount = 0;
				for (int i = 0; i < tissuesOfInterest.size(); i++) {
					finalSQL += " (tissue_chcID = ? AND tissue_tissueTypeID = ?) ";
					if (tissueCount < tissuesOfInterest.size() - 1) finalSQL += " OR ";
					tissueCount++;
				}
				finalSQL += " ) ";
				finalSQL += SQL_LIST_ORDERBYSTUB;
				preparedStatement = connection.prepareStatement(finalSQL);
				int valueCount = 1;
				for (Tissue tissueOfInterest : tissuesOfInterest) {
					preparedStatement.setInt(valueCount, tissueOfInterest.getChcID());
					valueCount++;
					preparedStatement.setString(valueCount, tissueOfInterest.getTissueTypeID());
					valueCount++;
				}
				resultSet = preparedStatement.executeQuery();
			}
			else {
				preparedStatement = connection.prepareStatement(SQL_LIST_ORDER_BY_CHCID_THEN_DATE);
				resultSet = preparedStatement.executeQuery();
			}
			while (resultSet.next()) {
				extractions.add(mapTissueExtraction(resultSet, true));
			}
			return extractions;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}
	}

	public List<TissueExtraction>
		listWithVariableParameters(HashMap<String, Object> queryParameters) throws DAOException {
		List<TissueExtraction> extractions = new ArrayList<TissueExtraction>();
		String finalQuery = SQL_LIST_EXTRACTION_QUERY_SELECT_STUB;

		ArrayList<Object> values = new ArrayList<Object>();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		if (queryParameters.keySet().size() > 0) {
			String whereQuery = " WHERE ";
			boolean firstKey = true;
			for (String key : queryParameters.keySet()) {
				if (key != null && key.length() > 0) {
					if (firstKey)
						firstKey = false;
					else
						whereQuery += " AND ";

					if (key.equals("chcID")) {
						whereQuery += " tissue_chcID = ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("chcIDLower")) {
						whereQuery += " tissue_chcID > ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("chcIDUpper")) {
						whereQuery += " tissue_chcID < ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("tissueTypeID")) {
						whereQuery += " tissue_tissueTypeID = ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("quantityTissueLeft")) {
						whereQuery += " quantityTissueLeftAfterManipulation = ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("quantityTissueLeftLower")) {
						whereQuery += " quantityTissueLeftAfterManipulation > ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("quantityTissueLeftUpper")) {
						whereQuery += " quantityTissueLeftAfterManipulation < ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("extractionType")) {
						whereQuery += " extractionType = ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("extractionDate")) {
						whereQuery += " dateExtraction = ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("extractionDateLower")) {
						whereQuery += " dateExtraction > ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("extractionDateUpper")) {
						whereQuery += " dateExtraction < ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("concentration")) {
						whereQuery += " concentration_ng_muL = ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("concentrationLower")) {
						whereQuery += " concentration_ng_muL > ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("concentrationUpper")) {
						whereQuery += " concentration_ng_muL < ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("qualityExtractionGel")) {
						whereQuery += " qualityExtractionGel = ? ";
						values.add(queryParameters.get(key));
					}
					else if (key.equals("emptyTube")) {
						whereQuery += " emptyTube = ? ";
						values.add(queryParameters.get(key));
					}
				}
			}
			finalQuery += whereQuery + SQL_LIST_EXTRACTION_QUERY_ORDER_STUB;
		}
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, finalQuery, false, values.toArray());
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				extractions.add(mapTissueExtraction(resultSet, false));
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}

		return extractions;
	}

	public List<TissueExtraction> listTissueExtractions(Tissue tissue) throws DAOException {
		Object[] values = { tissue.getChcID(), tissue.getTissueTypeID() };
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<TissueExtraction> tissueExtractions = new ArrayList<TissueExtraction>();
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_LIST_TISSUE_EXTRACTIONS, false, values);
			resultSet = statement.executeQuery();
			while (resultSet.next())
				tissueExtractions.add(mapTissueExtraction(resultSet, false));
			tissue.setTissueExtractions(tissueExtractions);
			return tissueExtractions;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
	}

	/**
	 * Create the given extraction in the database. The extraction ID must be null, otherwise it
	 * will throw an IllegalArgumentException. If the extraction ID value is unknown, rather use
	 * {@link #save(TissueExtraction)}. After creating, the DAO will set the obtained ID in the
	 * given extraction.
	 * 
	 * @param extraction
	 *            The extraction that will be created in the database.
	 * @throws IllegalArgumentException
	 *             If the extraction ID is not null.
	 * @throws DAOException
	 *             If something fails at database level.
	 */
	public void create(TissueExtraction extraction) throws DAOException {
		if (extraction.getManipulationID() != null) {
			throw new IllegalArgumentException(
				"Extraction is already created, the extractionID is not null.");
		}
		Object[] values =
			{ extraction.getTissueManipulated().getChcID(),
				extraction.getTissueManipulated().getTissueTypeID(),
				extraction.getExperimenter().getInitials(), extraction.getManipulationDate(),
				extraction.getFinalVolume(), extraction.getConcentration(),
				extraction.getQuantityExtracted(), extraction.getExtractionComment() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_INSERT, true, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0)
				throw new DAOException("Extraction creation failed, no rows affected.");
			generatedKeys = preparedStatement.getGeneratedKeys();
			if (generatedKeys.next())
				extraction.setManipulationID(generatedKeys.getLong(1));
			else
				throw new DAOException("Extraction creation failed, no generated keys returned.");
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, generatedKeys);
		}
	}

	public void update(TissueExtraction extraction) throws DAOException {
		if (extraction.getManipulationID() == null)
			throw new IllegalArgumentException(
				"Extraction is not created yet, the extraction ID is null.");
		Object[] values =
			{ extraction.getTissueManipulated().getChcID(),
				extraction.getTissueManipulated().getTissueTypeID(),
				extraction.getExperimenter().getInitials(), extraction.getManipulationDate(),
				extraction.getFinalVolume(), extraction.getConcentration(),
				extraction.getQuantityExtracted(), extraction.getExtractionComment(),
				extraction.getExtractionType(), extraction.getManipulationID() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_UPDATE, false, values);
			int affectedRow = preparedStatement.executeUpdate();
			if (affectedRow == 0)
				throw new DAOException("Extraction update failed, no rows affected.");
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement);
		}
	}

	public void updateWithVariableParameters(HashMap<String, String> requestFieldsAndValues)
		throws DAOException {

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int affectedRows = 0;

		if (!requestFieldsAndValues.containsKey("manipulationID"))
			throw new IllegalArgumentException("No manipulationID provided");

		String putativeManipulationID = requestFieldsAndValues.get("manipulationID");
		Long manipulationID = null;
		try {
			manipulationID = new Long(putativeManipulationID);
			if (!exists(new TissueExtraction(manipulationID)))
				throw new IllegalArgumentException("This extractionID (" + putativeManipulationID
					+ ") doesn't exist in the database.");
		}
		catch (NumberFormatException e) {
			throw new IllegalArgumentException("Invalid extractionID (" + putativeManipulationID
				+ ")");
		}

		LinkedHashMap<String, Integer> queryFieldsTypes = new LinkedHashMap<String, Integer>();

		// TABLE TISSUEEXTRACTION

		StringBuffer sql_update_extraction = new StringBuffer("UPDATE TissueExtraction SET ");
		HashMap<String, Integer> extractionFieldsTypes = new HashMap<String, Integer>();
		extractionFieldsTypes.put("concentration", Types.FLOAT);
		extractionFieldsTypes.put("finalVolume", Types.FLOAT);
		extractionFieldsTypes.put("quantityExtracted", Types.FLOAT);
		extractionFieldsTypes.put("emptyTube", Types.INTEGER);
		extractionFieldsTypes.put("gelQuality", Types.VARCHAR);
		extractionFieldsTypes.put("extractionComment", Types.VARCHAR);
		for (String field : extractionFieldsTypes.keySet()) {
			if (requestFieldsAndValues.containsKey(field)) {
				sql_update_extraction.append(field + " = ? ,");
				queryFieldsTypes.put(field, extractionFieldsTypes.get(field));
			}
		}
		sql_update_extraction.deleteCharAt(sql_update_extraction.length() - 1);
		sql_update_extraction.append(" WHERE extractionID = ?");

		try {
			connection = daoFactory.getConnection();
			preparedStatement = connection.prepareStatement(sql_update_extraction.toString());
			int argIndex = 1;
			for (String field : queryFieldsTypes.keySet()) {
				if (requestFieldsAndValues.get(field) != null
					&& requestFieldsAndValues.get(field).trim().length() > 0) {
					Object value = requestFieldsAndValues.get(field);
					int type = queryFieldsTypes.get(field);
					preparedStatement.setObject(argIndex, value, type);
				}
				else {
					preparedStatement.setNull(argIndex, queryFieldsTypes.get(field));
				}
				argIndex++;
			}
			preparedStatement.setLong(argIndex, manipulationID);
			affectedRows = preparedStatement.executeUpdate();
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement);
		}
		if (affectedRows == 0)
			throw new DAOException("TissueExtraction update failed, no row updated in Database.");

		queryFieldsTypes.clear();

		// TABLE TISSUEMANIPULATION

		StringBuffer sql_update_manipulation = new StringBuffer("UPDATE TissueManipulation SET ");
		HashMap<String, Integer> manipulationFieldsTypes = new HashMap<String, Integer>();
		manipulationFieldsTypes.put("quantityTissueLeftAfterManipulation", Types.VARCHAR);
		manipulationFieldsTypes.put("manipulationDate", Types.DATE);
		manipulationFieldsTypes.put("experimenter", Types.VARCHAR);
		for (String field : manipulationFieldsTypes.keySet()) {
			if (requestFieldsAndValues.containsKey(field)) {
				sql_update_manipulation.append(field + " = ? ,");
				queryFieldsTypes.put(field, manipulationFieldsTypes.get(field));
			}
		}
		sql_update_manipulation.deleteCharAt(sql_update_manipulation.length() - 1);
		sql_update_manipulation.append(" WHERE manipulationID = ?");

		try {
			connection = daoFactory.getConnection();
			preparedStatement = connection.prepareStatement(sql_update_manipulation.toString());
			int argIndex = 1;
			for (String field : queryFieldsTypes.keySet()) {
				if (requestFieldsAndValues.get(field) != null
					&& requestFieldsAndValues.get(field).trim().length() > 0) {
					Object value = requestFieldsAndValues.get(field);
					int type = queryFieldsTypes.get(field);
					preparedStatement.setObject(argIndex, value, type);
				}
				else {
					preparedStatement.setNull(argIndex, queryFieldsTypes.get(field));
				}
				argIndex++;
			}
			preparedStatement.setLong(argIndex, manipulationID);
			affectedRows = preparedStatement.executeUpdate();
		}
		catch (SQLException e) {
			if (e.getMessage().indexOf("Cannot add or update a child row") > -1
				&& e.getMessage().indexOf("fk_TissueManipulation_User") > -1) {
				throw new DAOException("user " + requestFieldsAndValues.get("experimenter")
					+ " doesn't exist yet in the database.");
			}
			else
				throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement);
		}
		if (affectedRows == 0)
			throw new DAOException("TissueManipulation update failed, no row updated in Database.");

		queryFieldsTypes.clear();

		// TABLE TISSUERNAEXTRACTION

		if (requestFieldsAndValues.containsKey("extractionType")
			&& requestFieldsAndValues.get("extractionType").equalsIgnoreCase("rna")) {

			StringBuffer sql_update_RNAextractions =
				new StringBuffer("UPDATE TissueRNAExtraction SET ");
			HashMap<String, Integer> rnaExtractionFieldsTypes = new HashMap<String, Integer>();
			rnaExtractionFieldsTypes.put("do260_do280", Types.FLOAT);
			rnaExtractionFieldsTypes.put("agilentDate", Types.DATE);
			rnaExtractionFieldsTypes.put("agilent_28S_18S", Types.FLOAT);
			rnaExtractionFieldsTypes.put("extractionMethod", Types.VARCHAR);

			for (String field : rnaExtractionFieldsTypes.keySet()) {
				if (requestFieldsAndValues.containsKey(field)) {
					sql_update_RNAextractions.append(field + " = ? ,");
					queryFieldsTypes.put(field, rnaExtractionFieldsTypes.get(field));
				}
			}
			sql_update_RNAextractions.deleteCharAt(sql_update_RNAextractions.length() - 1);
			sql_update_RNAextractions.append(" WHERE Extraction_extractionID = ?");

			try {
				connection = daoFactory.getConnection();
				preparedStatement =
					connection.prepareStatement(sql_update_RNAextractions.toString());
				int argIndex = 1;
				for (String field : queryFieldsTypes.keySet()) {
					if (requestFieldsAndValues.get(field) != null
						&& requestFieldsAndValues.get(field).trim().length() > 0) {
						Object value = requestFieldsAndValues.get(field);
						int type = queryFieldsTypes.get(field);
						preparedStatement.setObject(argIndex, value, type);
					}
					else {
						preparedStatement.setNull(argIndex, queryFieldsTypes.get(field));
					}
					argIndex++;
				}
				preparedStatement.setLong(argIndex, manipulationID);
				affectedRows = preparedStatement.executeUpdate();
			}
			catch (SQLException e) {
				throw new DAOException(e);
			}
			finally {
				close(connection, preparedStatement);
			}
		}

	}

	public void save(TissueExtraction extraction) throws DAOException {
		if (extraction.getManipulationID() == null)
			create(extraction);
		else
			update(extraction);
	}

	public void delete(TissueExtraction extraction) throws DAOException {
		Object[] values = { extraction.getManipulationID() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_DELETE, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0)
				throw new DAOException("Extraction deletion failed, no rows affected.");

		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement);
		}
	}

	public boolean exists(TissueExtraction extraction) throws DAOException {
		boolean exists = false;
		Object[] values = { extraction.getManipulationID() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_EXISTS_EXTRACTION, false, values);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			if (resultSet.getInt(1) > 0) exists = true;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}
		return exists;
	}

	public boolean existsSimilar(TissueExtraction extraction) throws DAOException {
		boolean exists = false;
		Object[] values =
			{ extraction.getTissueManipulated().getChcID(),
				extraction.getTissueManipulated().getTissueTypeID(), extraction.getConcentration(),
				extraction.getManipulationDate(), extraction.getExtractionType() };
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement =
				prepareStatement(connection,
					SQL_EXISTS_SIMILAR_EXTRACTION_BY_DATE_AND_CONCENTRATION, false, values);
			resultSet = statement.executeQuery();
			resultSet.next();
			if (resultSet.getInt(1) > 0) exists = true;

		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
		return exists;
	}

	public List<TissueExtraction> listSimilar(TissueExtraction extraction) throws DAOException {
		List<TissueExtraction> list = new ArrayList<TissueExtraction>();
		Object[] values =
			{ extraction.getTissueManipulated().getChcID(),
				extraction.getTissueManipulated().getTissueTypeID(), extraction.getConcentration(),
				extraction.getManipulationDate(), extraction.getExtractionType() };
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement =
				prepareStatement(connection, SQL_LIST_SIMILAR_EXTRACTION_BY_DATE_AND_CONCENTRATION,
					false, values);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				list.add(mapTissueExtraction(resultSet, true));
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}

		return list;
	}

	// Helpers ------------------------------------------------------------------------------------

	/**
	 * Map the current row of the given ResultSet to a TissueExtraction
	 * 
	 * @param resultSet
	 *            The ResultSet of which the current row is to be mapped to an Extraction
	 * @param mapRNA
	 *            TODO
	 * @return the mapped Extraction from the current row of the given ResultSet
	 * @throws SQLException
	 *             if something fails at database level.
	 */
	// protected static TissueExtraction mapTissueExtraction(ResultSet resultSet) throws
	// SQLException {
	// TissueExtraction extraction = new TissueExtraction(resultSet.getLong("extractionID"));
	//
	// extraction.setTissueManipulated(new Tissue(resultSet.getInt("tissue_chcID"),
	// resultSet.getString("tissue_tissueTypeID")));
	// extraction.setExperimenter(new User(resultSet.getString("experimenter")));
	// extraction.setManipulationDate(resultSet.getDate("manipulationDate"));
	// extraction.setFinalVolume(resultSet.getFloat("finalVolume"));
	// extraction.setConcentration(resultSet.getFloat("concentration"));
	// extraction.setQuantityExtracted(resultSet.getFloat("quantityExtracted"));
	// extraction.setExtractionType(resultSet.getString("extractionType"));
	// extraction.setQualityExtractionGel(resultSet.getString("gelQuality"));
	// extraction.setQuantityTissueLeftAfterManipulation(resultSet.getString("quantityTissueLeftAfterManipulation"));
	// extraction.setExtractionComment(resultSet.getString("extractionComment"));
	// extraction.setEmptyTube(resultSet.getInt("emptyTube") == 1);
	//
	// return extraction;
	// }

	protected static TissueExtraction mapTissueExtraction(ResultSet resultSet, Boolean mapRNA)
		throws SQLException {
		TissueExtraction extraction = null;
		if (resultSet.getString("extractionType").equalsIgnoreCase("RNA") && mapRNA) {
			extraction = new TissueExtractionRNA(resultSet.getLong("extractionID"));
			((TissueExtractionRNA) extraction).setDo260do280(resultSet.getFloat("do260_do280"));
			((TissueExtractionRNA) extraction).setAgilent28S18S(resultSet
				.getFloat("agilent_28S_18S"));
			((TissueExtractionRNA) extraction).setAgilentDate(resultSet.getDate("agilentDate"));
			((TissueExtractionRNA) extraction).setExtractionMethod(resultSet
				.getString("extractionMethod"));
		}
		else
			extraction = new TissueExtraction(resultSet.getLong("extractionID"));
		Tissue masterTissue = null;
		if (resultSet.getInt("masterTissue_chcID") > 0
			&& resultSet.getString("masterTissue_tissueTypeID") != null
			&& resultSet.getString("masterTissue_tissueTypeID").length() > 0) {
			masterTissue =
				new Tissue(resultSet.getInt("masterTissue_chcID"), resultSet
					.getString("masterTissue_tissueTypeID"));
		}
		extraction.setTissueManipulated(new Tissue(resultSet.getInt("tissue_chcID"), resultSet
			.getString("tissue_tissueTypeID"), masterTissue));
		extraction.setExperimenter(new User(resultSet.getString("experimenter")));
		extraction.setManipulationDate(resultSet.getDate("manipulationDate"));
		extraction.setFinalVolume(resultSet.getFloat("finalVolume"));
		extraction.setConcentration(resultSet.getFloat("concentration"));
		extraction.setQuantityExtracted(resultSet.getFloat("quantityExtracted"));
		extraction.setExtractionType(resultSet.getString("extractionType"));
		extraction.setQualityExtractionGel(resultSet.getString("gelQuality"));
		extraction.setQuantityTissueLeftAfterManipulation(resultSet
			.getString("quantityTissueLeftAfterManipulation"));
		extraction.setExtractionComment(resultSet.getString("extractionComment"));
		extraction.setEmptyTube(resultSet.getInt("emptyTube") == 1);

		return extraction;
	}

}
